class Choice < ActiveRecord::Base
  belongs_to :teacher
  belongs_to :student

  #  为导师提供申请学生的信息
  def self.get_student_invites(tea_id)
    find_by_sql(["
        SELECT s.id, s.sname, s.stype, c.stu_status
        FROM   choices c, students s
        WHERE  s.id = c.stu_id
        AND    c.stu_status = '同意' AND c.tea_status = '待定'
        AND    c.tea_id = ?
        ", tea_id] )
  end

  #  得到该教师已被学生选择的数量
  def self.invited_count(tea_id)
    count_by_sql(["
        SELECT count(stu_status) AS co
        FROM   choices
        WHERE  stu_status = '同意' AND tea_status = '待定' AND tea_id = ?
        ", tea_id ])
  end

  #  得到该教师与学生已完成双选的数量
  def self.finished_count(tea_id)
    count_by_sql(["
      SELECT count(tea_status) AS co
      FROM   choices
      WHERE  stu_status = '同意' AND tea_status = '同意' AND tea_id = ?
        ", tea_id ])
  end

  #  得到该教师的学生选择详细情况
  def self.stu_choice_details(tea_id)
    find_by_sql(["
        SELECT s.sname, t.tname, c.stu_status, c.tea_status
        FROM   students s, teachers t, choices c
        WHERE  s.id = c.stu_id AND t.id = c.tea_id AND c.tea_id = ?
        ", tea_id ])
  end

  #  得到该教师的录取学生详细情况
  def self.tea_choice_details(tea_id)
    find_by_sql(["
        SELECT s.sname, t.tname, c.stu_status, c.tea_status
        FROM   students s, teachers t, choices c
        WHERE  s.id = c.stu_id AND t.id = c.tea_id
        AND    c.stu_status = '同意' AND c.tea_status = '同意'
        AND    c.tea_id = ?
        ", tea_id ])
  end
  
  #  得到被该老师录取的学生名单
  def self.get_choice_student_names(tea_id)
    @names = find_by_sql(["
       SELECT s.sname
       FROM   students s, choice c
       WHERE  s.id = c.stu_id
       AND    c.stu_status = '同意' AND c.tea_status = '同意'
       AND    c.tea_id = ?
        ", tea_id ])
    sn = ""
    for name in @names
      sn += name.sname + "、"
    end
    sn
  end
end
